package com.dy.yunying.biz.dao.znfx;

import com.dy.yunying.api.dto.AdMaterialDataDTO;
import com.dy.yunying.api.dto.RoiTopTenDto;
import com.dy.yunying.api.vo.MaterialDataVO;
import com.dy.yunying.api.vo.RoiTopTenDaoVo;
import com.dy.yunying.api.vo.RoiTopTenVo;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

/**
 * 素材管理-数据报表
 * @author chenxiang
 * @className AdMaterialDataDao
 * @date 2023-2-27 15:06
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class CreateTopDataDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	private final YunYingProperties prop;

	/**
	 * 查询数据报表
	 * @param material
	 * @return
	 */
	public List<RoiTopTenDaoVo> roiTopTen(RoiTopTenDto material) {
		StringBuffer sqlBuilder = new StringBuffer();
		String roiString =  "";
		String orderBy =  "roi";
		switch(material.getTopType()){
			case 1:
				roiString = "fee_1";
				break;
			case 2:
				roiString = "fee_7";
				break;
			case 3:
				roiString = "fee_15";
				break;
			case 4:
				roiString = "fee_30";
				break;
			default:
				orderBy =  "cost";
				break;
		}
		if ("1".equals(material.getExperience())){
			this.experienceRoiDataSql(sqlBuilder,material,roiString);
		} else {
			this.roiDataSql(sqlBuilder, material,roiString);
		}
		sqlBuilder.append(" ORDER BY ").append(orderBy).append(" DESC LIMIT 10").append(" \n");
		final String sql = sqlBuilder.toString();
		log.debug("创意TOP10 SQL: [\n{}]", sql);
		long start = System.currentTimeMillis();
		final List<RoiTopTenDaoVo> list = clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(RoiTopTenDaoVo.class));
		long end = System.currentTimeMillis();
		log.info("创意TOP10 耗时: {}ms", end - start);
		return list;
	}

	private void roiDataSql(StringBuffer sql, RoiTopTenDto material,String roiString) {
		if(StringUtils.isBlank(roiString)){
			this.costDataSql(sql , material);
			return;
		}
		sql.append(" SELECT ").append(" \n");
		sql.append("     ROW_NUMBER() OVER (ORDER by roi desc) as sort, -- 排序 ").append(" \n");
		sql.append("     cid, -- 创意ID ").append(" \n");
		sql.append("     cost, -- 返点后消耗 ").append(" \n");
		sql.append("     round(if(cost > 0,divide(toFloat64(devicesharFee) * 100.00,cost),0),2) roi -- roi ").append(" \n");
		sql.append(" FROM ").append(" \n");
		sql.append("     ( ").append(" \n");
		sql.append("         SELECT ").append(" \n");
		sql.append("             cid, -- 创意ID ").append(" \n");
		sql.append("             COALESCE(SUM(reg.").append(roiString).append(" * sharing),0) devicesharFee ").append(" \n");
		sql.append("         FROM ").append(" \n");
		sql.append("             ( ").append(" \n");
		sql.append("                 SELECT ").append(" \n");
		sql.append(roiString).append(" \n");
		sql.append("                 ,cid,game_main pgid ").append(" \n");
		sql.append("                 FROM ").append(" \n");
		sql.append("                 ").append(prop.getNinetydeviceregtable()).append(" \n");
		sql.append("                 WHERE ").append(" \n");
		sql.append("                     spread_type = 1 AND cid <> ''  AND reg_day >= ").append(material.getSdate()).append(" AND reg_day <= ").append(material.getEdate()).append(" \n");
		sql.append("                     ").append(this.getChlWhereSql(material,"chl_main")).append(" \n");
		sql.append("             ) reg ").append(" \n");
		sql.append("             LEFT JOIN (SELECT CAST(id AS Int16) AS id,(1 - COALESCE(sharing,0)) sharing FROM dim_200_pangu_mysql_parent_game pg_tmp) pg ON reg.pgid = pg.id ").append(" \n");
		sql.append("         GROUP BY cid ").append(" \n");
		sql.append("     ) a ").append(" \n");
		sql.append("     FULL JOIN ( ").append(" \n");
		sql.append(" SELECT ").append(" \n");
		sql.append("     creative_id cid, -- 设备激活数 ").append(" \n");
		sql.append("     toFloat64(COALESCE(SUM(cost),0)) cost -- 返点后消耗 ").append(" \n");
		sql.append(" FROM ").append(" \n");
		sql.append(" ").append(prop.getCreativeDayReport()).append(" \n");
		sql.append(" WHERE ").append(" \n");
		sql.append("      day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("     ").append(this.getChlWhereSql(material,"parentchl")).append(" \n");
		sql.append(" GROUP BY creative_id ").append(" \n");
		sql.append("     ) reg USING (cid) ").append(" \n");
	}
	private void costDataSql(StringBuffer sql, RoiTopTenDto material) {
		sql.append(" SELECT ").append(" \n");
		sql.append("     ROW_NUMBER() OVER (ORDER by cost desc) as sort, -- 排序 ").append(" \n");
		sql.append("     creative_id cid, -- 创意id ").append(" \n");
		sql.append("     toFloat64(COALESCE(SUM(cost),0)) cost -- 返点后消耗 ").append(" \n");
		sql.append(" FROM ").append(" \n");
		sql.append(" ").append(prop.getCreativeDayReport()).append(" \n");
		sql.append(" WHERE ").append(" \n");
		sql.append("      day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("     ").append(this.getChlWhereSql(material,"parentchl")).append(" \n");
		sql.append(" GROUP BY creative_id ").append(" \n");
	}

	/**
	 * 体验版
	 * @param sql
	 * @param material
	 */
	private void experienceRoiDataSql(StringBuffer sql, RoiTopTenDto material,String roiString) {
		if(StringUtils.isBlank(roiString)){
			this.experienceCostDataSql(sql , material);
			return;
		}
		sql.append(" SELECT ").append(" \n");
		sql.append("       ROW_NUMBER() OVER (ORDER by roi desc) as sort, -- 排序 ").append(" \n");
		sql.append("     cid, -- 广告ID ").append(" \n");
		sql.append("     cost, -- 返点后消耗 ").append(" \n");
		sql.append("     adid, --  ").append(" \n");
		sql.append("     round(if(cost > 0,divide(toFloat64(devicesharFee) * 100.00,cost),0),2) roi -- roi ").append(" \n");
		sql.append(" FROM ").append(" \n");
		sql.append("     ( ").append(" \n");
		sql.append("         SELECT ").append(" \n");
		sql.append("             cid, -- 创意ID ").append(" \n");
		sql.append("             adid, ").append(" \n");
		sql.append("             COALESCE(SUM(reg.").append(roiString).append(" * sharing),0) devicesharFee ").append(" \n");
		sql.append("         FROM ").append(" \n");
		sql.append("             ( ").append(" \n");
		sql.append("                 SELECT ").append(" \n");
		sql.append(roiString).append(" \n");
		sql.append("                     ,CAST(mid3 AS UInt64) cid,CAST(ad_id AS UInt64) adid,game_main pgid ").append(" \n");
		sql.append("                 FROM ").append(" \n");
		sql.append("                     ").append(prop.getNinetydeviceregtable()).append(" \n");
		sql.append("                 WHERE ").append(" \n");
		sql.append("                      spread_type = 1 AND mid3 <> '' AND reg_day >= ").append(material.getSdate()).append(" AND reg_day <= ").append(material.getEdate()).append(" \n");
		sql.append("                     ").append(this.getChlWhereSql(material,"chl_main")).append(" \n");
		sql.append("             ) reg ").append(" \n");
		sql.append("             LEFT JOIN (SELECT CAST(id AS Int16) AS id,(1 - COALESCE(sharing,0)) sharing FROM dim_200_pangu_mysql_parent_game pg_tmp) pg ON reg.pgid = pg.id ").append(" \n");
		sql.append("         GROUP BY cid ,adid").append(" \n");
		sql.append("     ) a ").append(" \n");
		sql.append("     FULL JOIN ( ").append(" \n");
		sql.append("       SELECT ").append(" \n");
		sql.append("       material_id cid, -- 素材id ").append(" \n");
		sql.append("       promotion_id adid, ").append(" \n");
		sql.append("       toFloat64(COALESCE(SUM(cost),0)) cost -- 返点后消耗 ").append(" \n");
		sql.append("       FROM ").append(" \n");
		sql.append("       ").append(prop.getMaterialDayReport()).append(" \n");
		sql.append("       WHERE ").append(" \n");
		sql.append("       day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("       ").append(this.getChlWhereSql(material,"parentchl")).append(" \n");
		sql.append("       GROUP BY material_id,promotion_id ").append(" \n");
		sql.append("     ) reg USING (cid,adid) ").append(" \n");
	}
	private void experienceCostDataSql(StringBuffer sql, RoiTopTenDto material) {
		sql.append(" SELECT ").append(" \n");
		sql.append(" ROW_NUMBER() OVER (ORDER by cost desc) as sort, -- 排序 ").append(" \n");
		sql.append(" material_id cid, -- 素材id ").append(" \n");
		sql.append(" promotion_id adid, ").append(" \n");
		sql.append(" toFloat64(COALESCE(SUM(cost),0)) cost -- 返点后消耗 ").append(" \n");
		sql.append(" FROM ").append(" \n");
		sql.append(" ").append(prop.getMaterialDayReport()).append(" \n");
		sql.append(" WHERE ").append(" \n");
		sql.append(" day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append(" ").append(this.getChlWhereSql(material,"parentchl")).append(" \n");
		sql.append(" GROUP BY material_id,promotion_id ").append(" \n");
	}

	private String getChlWhereSql(RoiTopTenDto material,String parent) {
		final String parentchl = StringUtils.trim(material.getParentchl());
		final StringBuilder builder = new StringBuilder();
		if (StringUtils.isNotBlank(parentchl)) {
			builder.append(" AND ").append(parent).append(" IN ('").append(parentchl.replaceAll(",", "','")).append("')");
		}
		return builder.toString();
	}


}
